/**
 * 
 */
package com.ken.work.tools.tools.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.ken.work.tools.tools.Contants;
import com.ken.work.tools.tools.model.ColumnModel;
import com.ken.work.tools.tools.utils.DataTypeUtil;
import com.ken.work.tools.tools.utils.UnderlineCamel;

/**
 * @desc {元数据库操作}
 * @author Liuk
 * @date 2017年4月20日 下午5:23:34
 * @version v1.0-
 */
public class DBUtil {
	
	

	private static Connection conn;
	private static PreparedStatement ps;
	private static ResultSet rs;

	/**
	 * 查询数据库中的全部表名
	 * 
	 * @return
	 */
	public static List<String> getTableName() {
		conn = getConnection();
		List<String> tableNameList = null;
		try {
			DatabaseMetaData dbMetaData = conn.getMetaData();
			String[] types = { "TABLE" };
			ResultSet rs = dbMetaData.getTables(conn.getCatalog(), null, "%", types/* 只要表就好了 */);
			tableNameList = new ArrayList<String>();
			while (rs.next()) {
				tableNameList.add(rs.getString("TABLE_NAME"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return tableNameList;
	}
	
	public static String getTableNote(String tableName){
			conn = getConnection();
		 
			String tableNote = null;
	        try {
	            DatabaseMetaData dbmd = conn.getMetaData();
	            ResultSet resultSet = dbmd.getTables(null, "%", "%", new String[] { "TABLE" });
	            while (resultSet.next()) {
	                String oneTableName=resultSet.getString("TABLE_NAME");
	                if(oneTableName.equalsIgnoreCase(tableName)) {
	                	tableNote = resultSet.getString("REMARKS");
	                	return  tableNote;
	                }
	                
	                
	            }
	        } catch (Exception e) {
	            e.printStackTrace();
	        }finally {
				closeConnection();
			}
	        
	        return tableNote;
	    }

	/**
	 * 查询表中的
	 * 
	 * @param tableName
	 * @return
	 */
	public static List<ColumnModel> getCols(String tableName) {
		conn = getConnection();
		List<ColumnModel> list = new ArrayList<ColumnModel>();
		try {
			DatabaseMetaData dbMetaData = conn.getMetaData();
			ResultSet rs = dbMetaData.getColumns(conn.getCatalog(), "%", tableName, "%");
			while (rs.next()) {
				ColumnModel model = new ColumnModel();
				String cloumnName = rs.getString("COLUMN_NAME");
				cloumnName = UnderlineCamel.underline2Camel(cloumnName, true,Contants.table_pre);
				model.setColumnName(cloumnName);
				model.setMethodName(UnderlineCamel.getFirstUpper(cloumnName));
				model.setColumnType(DataTypeUtil.getJavaType(rs.getString("TYPE_NAME")));
				
				model.setDbColumnType(rs.getString("TYPE_NAME"));
				
				//取长度有问题，（数字类型会和db不一致（mysql）,所以只有varchar 和 decimal 保留长度，其他得就不要了）
				Integer fieldLength = Integer.valueOf(rs.getString("COLUMN_SIZE"));
				
				
				
				if("varchar".equalsIgnoreCase(model.getDbColumnType())
						||"decimal".equalsIgnoreCase(model.getDbColumnType())) {
					if(fieldLength != null)
						model.setDbColumnLength(fieldLength);
					
					if("decimal".equalsIgnoreCase(model.getDbColumnType())) {
						Integer decimalDigits = Integer.valueOf(rs.getString("DECIMAL_DIGITS"));
						model.setDecimalDigits(decimalDigits);
					}
				}
				
				String remarks = rs.getString("REMARKS");
				model.setRemarks(remarks);
				list.add(model);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return list;
	}

	

	public static Connection getConnection() {
		try {
			Class.forName(Contants.DB.driver);
			
			Properties connectionProps = new Properties();
	        // ...
	        /**
	         * If load remarks of table using java.sql.DatabaseMetaData, need to set these parameters.
	         */
	        connectionProps.put("remarks", "true");
	        connectionProps.put("useInformationSchema", "true");
	 
	
	       
	        	connectionProps.put("user", Contants.DB.username);
	     
	       
	        	connectionProps.put("password", Contants.DB.password);
	       
	        
			
//			conn = DriverManager.getConnection(Contants.DB.url, Contants.DB.username, Contants.DB.password);
	        conn = DriverManager.getConnection(Contants.DB.url,connectionProps);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void closeConnection() {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}
